<?php

class m_kategoribarang extends CI_Model {

    function __construct() {
        // Call the Model constructor
        parent::__construct();
    }

    function get_gpbarang_cd_by_kategoribarang_cd($params) {
        $sql = "SELECT a.gpbarang_cd FROM kategoribarang a
                WHERE a.kategoribarang_cd = ?";
        $query = $this->db->query($sql, $params);
        if ($query->num_rows() > 0) {
            $result = $query->row_array();
            $query->free_result();
            return $result['gpbarang_cd'];
        } else {
            return array();
        }
    }

    function get_count_all_kategoribarang() {
        $sql = "SELECT COUNT(kategoribarang_cd)'total' FROM kategoribarang";
        $query = $this->db->query($sql);
        if ($query->num_rows() > 0) {
            $result = $query->row_array();
            $query->free_result();
            return $result['total'];
        } else {
            return array();
        }
    }

    //cek data
    function get_count_all_kategoribarang_by_kode($params) {
        $sql = "SELECT COUNT(*) 'total' FROM kategoribarang WHERE kategoribarang_cd = ?";
        $query = $this->db->query($sql, $params);
        if ($query->num_rows() > 0) {
            $result = $query->row_array();
            $query->free_result();
            return $result['total'];
        } else {
            return array();
        }
    }

    //cek data
    function get_count_all_kategoribarang_by_kode_nama($params) {
        $sql = "SELECT COUNT(*) 'total' FROM kategoribarang WHERE kategoribarang_cd = ? AND kategoribarang_name = ? AND mdb =?";
        $query = $this->db->query($sql, $params);
        if ($query->num_rows() > 0) {
            $result = $query->row_array();
            $query->free_result();
            return $result['total'];
        } else {
            return array();
        }
    }

    // get user profil
    function get_all_kategoribarang() {
        $sql = "SELECT a.*, b.gpbarang_name FROM kategoribarang a
                JOIN group_barang b ON a.gpbarang_cd = b.gpbarang_cd
                ORDER BY CAST(kategoribarang_cd AS UNSIGNED) ASC";
        $query = $this->db->query($sql);
        if ($query->num_rows() > 0) {
            $result = $query->result_array();
            $query->free_result();
            return $result;
        } else {
            return array();
        }
    }

    function get_all_kategoribarang_pagination($params) {
        $sql = "SELECT a.*, b.gpbarang_name FROM kategoribarang a
                JOIN group_barang b ON a.gpbarang_cd = b.gpbarang_cd 
                ORDER BY a.kategoribarang_cd 
                LIMIT ?, ?";
        $query = $this->db->query($sql, $params);
        if ($query->num_rows() > 0) {
            $result = $query->result_array();
            $query->free_result();
            return $result;
        } else {
            return array();
        }
    }

    function get_kategoribarang_by_id($params) {
        $sql = "SELECT a.*, b.gpbarang_name FROM kategoribarang a
                JOIN group_barang b ON a.gpbarang_cd = b.gpbarang_cd
                WHERE a.kategoribarang_cd = ?";
        $query = $this->db->query($sql, $params);
        if ($query->num_rows() > 0) {
            $result = $query->row_array();
            $query->free_result();
            return $result;
        } else {
            return array();
        }
    }

    // insert
    function insert($user_id) {
        // update by this date
        $sql = "INSERT INTO kategoribarang VALUES (?, ?, ?, ?, ?, NOW())";
        return $this->db->query($sql, $user_id);
    }

    // edit
    function edit($user_id) {
        // update by this date
        $sql = "UPDATE kategoribarang SET kategoribarang_name = ?, gpbarang_cd = ?, kategoribarang_susut = ?, mdb = ? WHERE kategoribarang_cd = ?";
        return $this->db->query($sql, $user_id);
    }

    // delete
    function delete($user_id) {
        // update by this date
        $sql = "DELETE FROM kategoribarang WHERE kategoribarang_cd = ?";
        return $this->db->query($sql, $user_id);
    }

    /*
     * LAPORAN
     */

    function get_all_kategori_laporan($param) {
        $sql = "SELECT a.*, COUNT(b.barang_id) AS 'total_barang', 
                SUM(b.barang_harga_beli) AS 'total_harga_beli', 
                SUM(b.barang_nilai_buku) AS 'total_nilai_buku' 
                FROM kategoribarang a
                LEFT JOIN barang b ON a.kategoribarang_cd = b.kategoribarang_cd
                WHERE a.gpbarang_cd = ?
                GROUP BY a.kategoribarang_cd";
        $query = $this->db->query($sql, $param);
        if ($query->num_rows() > 0) {
            $result = $query->result_array();
            $query->free_result();
            return $result;
        } else {
            return array();
        }
    }

    function get_all_kategori_by_lokasi_cd_laporan($param) {
        $sql = "SELECT a.*, 
                COUNT(b.barang_id) AS 'total_barang', 
                SUM(b.barang_harga_beli) AS 'total_harga_beli', 
                SUM(b.barang_nilai_buku) AS 'total_nilai_buku' 
                FROM kategoribarang a
                LEFT JOIN barang b ON a.kategoribarang_cd = b.kategoribarang_cd
                WHERE b.lokasi_cd = ? 
                GROUP BY b.lokasi_cd";
        $query = $this->db->query($sql, $param);
        if ($query->num_rows() > 0) {
            $result = $query->result_array();
            $query->free_result();
            return $result;
        } else {
            return array();
        }
    }

    /*
     * GRAPH
     */

    function get_all_kategori_graph() {
        $sql = "SELECT a.*, COUNT(b.barang_id) AS 'total_barang', 
                SUM(b.barang_harga_beli) AS 'total_harga_beli', 
                SUM(b.barang_nilai_buku) AS 'total_nilai_buku',
                c.gpbarang_name, c.gpbarang_cd
                FROM kategoribarang a
                LEFT JOIN barang b ON a.kategoribarang_cd = b.kategoribarang_cd
                LEFT JOIN group_barang c ON a.gpbarang_cd = c.gpbarang_cd
                GROUP BY a.kategoribarang_cd 
                ORDER BY c.gpbarang_cd ASC";
        $query = $this->db->query($sql);
        if ($query->num_rows() > 0) {
            $result = $query->result_array();
            $query->free_result();
            return $result;
        } else {
            return array();
        }
    }

}
